import openpyxl
import numpy as np

from QuickTools.tools_pmc import (lj_wlxxb
                                  )

wb_wlxxb = openpyxl.load_workbook(lj_wlxxb)
ws_wlxxb = wb_wlxxb.worksheets[0]

wb = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\5，销售预测准确率\2025年销售预测准确率.xlsx")
ws1 = wb["月度"]
ws2 = wb["均值折线图"]
# ws_规格同权 = wb["规格同权"]
# ws_规格同权老版公式 = wb["规格同权+老版公式"]
# ws_老版公式 = wb["老版公式"]

# 规格信息修补
def tool1():

    set1 = set()
    for i in range(11700, ws_wlxxb.max_row + 1):
        if 300000000000 < int(ws_wlxxb.cell(i, 1).value) < 303000000000 or \
                500000000000 < int(ws_wlxxb.cell(i, 1).value):
            set1.add(ws_wlxxb.cell(i, 3).value)

    set2 = set()
    for i in range(3, ws1.max_row + 1):
        set2.add(ws1.cell(i, 1).value)
    set_new = set1.difference(set2)
    a = ws1.max_row + 1
    for i in set_new:
        ws1.cell(a, 1).value = i
        for j in range(3, ws_wlxxb.max_row + 1):
            if ws_wlxxb.cell(j, 3).value == ws1.cell(a, 1).value:
                ws1.cell(a, 2).value = ws_wlxxb.cell(j, 2).value
                continue
        for j in range(3, ws1.max_column + 1):
            if ws1.cell(2, j).value is not None:
                ws1.cell(a, j).value = 0
        a += 1

    wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\5，销售预测准确率\2025年销售预测准确率.xlsx")


def tool2(x: int):
    j_sv = 5 * x  # 销货数量列号
    j_acc1 = 1 + 5 * x  # 销售预测准确率
    j_acc2 = 2 + 5 * x  # 销售预测调整后准确率
    sum_sv = 0  # 总销货数量
    sum_acc1 = 0
    sum_acc2 = 0
    for i in range(3, ws1.max_row + 1):
        if ws1.cell(i, j_sv).value == 0 and ws1.cell(i, j_sv - 1).value == 0 and ws1.cell(i, j_sv - 2).value == 0:
            ws1.cell(i, j_acc1).value = 0
            ws1.cell(i, j_acc2).value = 0
        else:
            try:
                ws1.cell(i, j_acc1).value = (min(ws1.cell(i, 5 * x).value, ws1.cell(i, 5 * x - 2).value)
                                             / max(ws1.cell(i, 5 * x).value, ws1.cell(i, 5 * x - 2).value))
            except ZeroDivisionError:
                ws1.cell(i, j_acc1).value = 0

            try:
                ws1.cell(i, j_acc2).value = (
                        min(ws1.cell(i, 5 * x).value, ws1.cell(i, 5 * x - 2).value + ws1.cell(i, 5 * x - 1).value)
                        / max(ws1.cell(i, 5 * x).value, ws1.cell(i, 5 * x - 2).value + ws1.cell(i, 5 * x - 1).value))
            except ZeroDivisionError:
                ws1.cell(i, j_acc2).value = 0

    # for i in range(3, ws1.max_row + 1):
    #     sum_sv += ws1.cell(i, j_sv).value
    #     sum_acc1 = (sum_acc1 + max(ws1.cell(i, 5 * x - 2).value, ws1.cell(i, 5 * x).value) *
    #                 ws1.cell(i, 5 * x + 1).value)
    #     sum_acc2 = (sum_acc2 + max(ws1.cell(i, 5 * x - 2).value + ws1.cell(i, 5 * x - 1).value,
    #                                ws1.cell(i, 5 * x).value) *
    #                 ws1.cell(i, 5 * x + 2).value)
    # ws2.cell(x + 2, 2).value = sum_acc1 / sum_sv
    # ws2.cell(x + 2, 3).value = sum_acc2 / sum_sv
    for i in range(3, ws1.max_row + 1):
        sum_sv += ws1.cell(i, j_sv - 2).value
        sum_acc1 = sum_acc1 + ws1.cell(i, 5 * x - 2).value * ws1.cell(i, 5 * x + 1).value
        sum_acc2 = sum_acc2 + ws1.cell(i, 5 * x - 2).value * ws1.cell(i, 5 * x + 2).value
    print(sum_sv, sum_acc1, sum_acc2)
    ws2.cell(x + 2, 2).value = sum_acc1 / sum_sv
    ws2.cell(x + 2, 3).value = sum_acc2 / sum_sv
    wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\5，销售预测准确率\2025年销售预测准确率.xlsx")


#
# tool1()
tool2(2)


# def 规格同权sheet计算(x: int):
#     for i in range(1, x + 1):
#         j_sv = 5 * i  # 销货数量列号
#         j_acc1 = 1 + 5 * i  # 销售预测准确率
#         j_acc2 = 2 + 5 * i  # 销售预测调整后准确率
#         sum_sv = 0  # 总销货数量
#         sum_acc1 = 0
#         sum_acc2 = 0
#         for j in range(18, ws_规格同权.max_row + 1):
#             if ws_规格同权.cell(j, 5 * i).value != 0 or ws_规格同权.cell(j, 5 * i - 2).value != 0 or ws_规格同权.cell(
#                     j,
#                     5 * i - 1).value != 0:
#                 sum_sv += 1
#                 sum_acc1 = sum_acc1 + ws_规格同权.cell(j, 5 * i + 1).value
#                 sum_acc2 = sum_acc2 + ws_规格同权.cell(j, 5 * i + 2).value
#         print(sum_sv, sum_acc1, sum_acc2)
#         ws_规格同权.cell(i + 2, 3).value = sum_acc1 / sum_sv
#         ws_规格同权.cell(i + 2, 4).value = sum_acc2 / sum_sv
#     wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\5，销售预测准确率\2024年销售预测准确率.xlsx")
#
#
# # 规格同权sheet计算(10)
#
#
# def 规格同权老版公式sheet计算(x: int):
#     for i in range(1, x + 1):
#         print(i)
#         j_sv = 5 * i  # 销货数量列号
#         j_acc1 = 1 + 5 * i  # 销售预测准确率
#         j_acc2 = 2 + 5 * i  # 销售预测调整后准确率
#         print(j_sv, j_acc1, j_acc2)
#         for j in range(18, ws_规格同权老版公式.max_row + 1):
#             if (ws_规格同权老版公式.cell(j, j_sv).value == 0
#                     and ws_规格同权老版公式.cell(j, j_sv - 1).value == 0
#                     and ws_规格同权老版公式.cell(j, j_sv - 2).value == 0):
#                 ws_规格同权老版公式.cell(j, j_acc1).value = 0
#                 ws_规格同权老版公式.cell(j, j_acc2).value = 0
#             else:
#                 try:
#                     ws_规格同权老版公式.cell(j, j_acc1).value = (1 - abs(
#                         ws_规格同权老版公式.cell(j, 5 * i).value -
#                         ws_规格同权老版公式.cell(j, 5 * i - 2).value
#                     ) / ws_规格同权老版公式.cell(j, 5 * i - 2).value)
#                 except ZeroDivisionError:
#                     ws_规格同权老版公式.cell(j, j_acc1).value = 0
#                 try:
#                     ws_规格同权老版公式.cell(j, j_acc2).value = (1 - abs(
#                         ws_规格同权老版公式.cell(j, 5 * i).value -
#                         ws_规格同权老版公式.cell(j, 5 * i - 2).value -
#                         ws_规格同权老版公式.cell(j, 5 * i - 1).value
#                     ) / (ws_规格同权老版公式.cell(j, 5 * i - 2).value + ws_规格同权老版公式.cell(j, 5 * i - 1).value))
#                 except ZeroDivisionError:
#                     ws_规格同权老版公式.cell(j, j_acc2).value = 0
#         print(ws_规格同权老版公式.cell(22, 7).value)
#
#         sum_sv = 0  # 总销货数量
#         sum_acc1 = 0
#         sum_acc2 = 0
#         for j in range(18, ws_规格同权老版公式.max_row + 1):
#             if ws_规格同权老版公式.cell(j, 5 * i).value != 0 or ws_规格同权老版公式.cell(j,
#                                                                                           5 * i - 2).value != 0 or ws_规格同权老版公式.cell(
#                     j,
#                     5 * i - 1).value != 0:
#                 sum_sv += 1
#                 sum_acc1 = sum_acc1 + ws_规格同权老版公式.cell(j, 5 * i + 1).value
#                 sum_acc2 = sum_acc2 + ws_规格同权老版公式.cell(j, 5 * i + 2).value
#         # print(sum_sv, sum_acc1, sum_acc2)
#         ws_规格同权老版公式.cell(i + 2, 3).value = sum_acc1 / sum_sv
#         ws_规格同权老版公式.cell(i + 2, 4).value = sum_acc2 / sum_sv
#     wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\5，销售预测准确率\2024年销售预测准确率.xlsx")
#
#
# # 规格同权老版公式sheet计算(10)
#
# def 老版公式sheet计算(x: int):
#     for i in range(1, x + 1):
#         j_sv = 5 * i  # 销货数量列号
#         j_acc1 = 1 + 5 * i  # 销售预测准确率
#         j_acc2 = 2 + 5 * i  # 销售预测调整后准确率
#         for j in range(18, ws_老版公式.max_row + 1):
#             if (ws_老版公式.cell(j, j_sv).value == 0
#                     and ws_老版公式.cell(j, j_sv - 1).value == 0
#                     and ws_老版公式.cell(j, j_sv - 2).value == 0):
#                 ws_老版公式.cell(j, j_acc1).value = 0
#                 ws_老版公式.cell(j, j_acc2).value = 0
#             else:
#                 try:
#                     ws_老版公式.cell(j, j_acc1).value = (1 - abs(
#                         ws_老版公式.cell(j, 5 * i).value -
#                         ws_老版公式.cell(j, 5 * i - 2).value
#                     ) / ws_老版公式.cell(j, 5 * i - 2).value)
#                 except ZeroDivisionError:
#                     ws_老版公式.cell(j, j_acc1).value = 0
#                 try:
#                     ws_老版公式.cell(j, j_acc2).value = (1 - abs(
#                         ws_老版公式.cell(j, 5 * i).value -
#                         ws_老版公式.cell(j, 5 * i - 2).value -
#                         ws_老版公式.cell(j, 5 * i - 1).value
#                     ) / (ws_老版公式.cell(j, 5 * i - 2).value + ws_老版公式.cell(j, 5 * i - 1).value))
#                 except ZeroDivisionError:
#                     ws_老版公式.cell(j, j_acc2).value = 0
#
#         sum_sv = 0  # 总销货数量
#         sum_acc1 = 0
#         sum_acc2 = 0
#         for j in range(18, ws_老版公式.max_row + 1):
#             if (ws_老版公式.cell(j, 5 * i).value != 0 or
#                     ws_老版公式.cell(j, 5 * i - 2).value != 0 or
#                     ws_老版公式.cell(j, 5 * i - 1).value != 0):
#                 sum_sv += ws_老版公式.cell(j, j_sv - 2).value
#                 sum_acc1 = sum_acc1 + ws_老版公式.cell(j, 5 * i - 2).value * ws_老版公式.cell(j, 5 * i + 1).value
#                 sum_acc2 = sum_acc2 + ws_老版公式.cell(j, 5 * i - 2).value * ws_老版公式.cell(j, 5 * i + 2).value
#         # print(sum_sv, sum_acc1, sum_acc2)
#         ws_老版公式.cell(i + 2, 3).value = sum_acc1 / sum_sv
#         ws_老版公式.cell(i + 2, 4).value = sum_acc2 / sum_sv
#     wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\5，销售预测准确率\2024年销售预测准确率.xlsx")

# 老版公式sheet计算(10)
